from datetime import datetime
from bodhi.contrib.sessions.base import SessionStoreBase

from sqlalchemy.exc import IntegrityError, ProgrammingError
from sqlalchemy import text

class PGSessionStore(SessionStoreBase):
    """
    Implements postgresql session store.

    The following table should be created beforehand.
    
    create table sessions (
        session_id varchar(64) not null primary key,
        session_data character varying(2048),
        expire_date timestamp without time zone
    );

    Example:

    from bodhi.contrib.sessions import SessionFilter, Session
    from bodhi.contrib.sessions.auth import AuthProvider
    from bodhi.contrib.sessions.db import PGSessionStore

    def session_factory(session_id, session_age):
        return Session(PGSessionStore(DBSession, session_age=session_age), session_id)

    class BaseView(View):
        _expose_ = False

        _filters_ = [GZipFilter(), 
                    ETagFilter(), 
                    CsrfFilter(), 
                    SessionFilter(session_factory),
                    TransactionFilter(DBSession)]

        def _get_auth_provider(self):
            def get_user(user_id):
                user = User.by_id(user_id)
                if user:
                    DBSession.expunge(user) # user may be used by more than one request.
                return user
            return AuthProvider(get_user)
    """
    def __init__(self, db_session, **kwargs):
        super().__init__(**kwargs)
        self.db_session = db_session

    def load(self, session_id):
        result = self.db_session.execute(text('select * from sessions where session_id = :session_id and expire_date > :expire_date'),
                {'session_id': session_id, 'expire_date': datetime.utcnow()})
        row = result.fetchone()
        if row is not None:
            return self.decode(row.session_data)

    def create(self, data=None):
        for i in range(3):
            session_id = self.get_new_session_id()
            #result = self.db_session.execute(text('select count(*) from sessions where session_id = :session_id'),
            #        {'session_id': session_id})
            #if result.fetchone()[0] != 0:
            #    continue
            
            params = dict(
                session_id = session_id,
                session_data = self.encode(data),
                expire_date = self.get_expiry_date()
            )

            self.db_session.begin_nested() # pysite, pg8000 does not support savepoint correctly.
            try:
                self.db_session.execute('insert into sessions (session_id, session_data, expire_date) values(:session_id, :session_data, :expire_date)',
                        params)
                self.db_session.commit() # commit nested transaction
                return session_id
            except IntegrityError: 
                # Key wasn't unique. Try again.
                self.db_session.rollback()
        else:
            raise RuntimeError('failed create session.')

    def save(self, session_id, data):
        params = dict(
            session_id = session_id,
            session_data = self.encode(data) if data else '',
            expire_date = self.get_expiry_date()
        )
        sql = 'update sessions set(session_data, expire_date) = (:session_data, :expire_date) where session_id = :session_id'
        self.db_session.execute(sql, params)

    def renew(self, session_id):
        params = dict(
            session_id = session_id,
            expire_date = self.get_expiry_date()
        )
        sql = 'update sessions set(expire_date) = (:expire_date) where session_id = :session_id'
        self.db_session.execute(sql, params)

    def delete(self, session_id):
        if session_id is None:
            return
        self.db_session.execute('delete from sessions where session_id = :session_id', {'session_id': session_id})
        # delete will not raise exception, result.rowcount: how many rows are deleted

